﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class BranchService
    {
        /// <summary>
        /// 查询出所有的机构信息
        /// </summary>
        /// <returns></returns>
        public List<Branch> GetAllBranch()
        {
            string sql = "select * from Branch";
            List<Branch> list = new List<Branch>();
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Branch branch = new Branch();
                    branch.BranchId = Convert.ToInt32(dr["BranchId"]);
                    branch.BranchName = dr["BranchName"].ToString();
                    branch.BranchShortName = dr["BranchShortName"].ToString();
                    branch.PrincipalUser = dr["PrincipalUser"].ToString();
                    branch.ConnectTelNo = dr["ConnectTelNo"].ToString();
                    branch.ConnectMobileTelNo = dr["ConnectMobileTelNo"].ToString();
                    branch.Faxes = dr["Faxes"].ToString();
                    list.Add(branch);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Branch GetOneBranch(int id)
        {
            string sql = string.Format("select * from Branch where BranchId={0}", id);
            List<Branch> list = new List<Branch>();
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Branch branch = new Branch();
                    branch.BranchId = Convert.ToInt32(dr["BranchId"]);
                    branch.BranchName = dr["BranchName"].ToString();
                    branch.BranchShortName = dr["BranchShortName"].ToString();
                    branch.PrincipalUser = dr["PrincipalUser"].ToString();
                    branch.ConnectTelNo = dr["ConnectTelNo"].ToString();
                    branch.ConnectMobileTelNo = dr["ConnectMobileTelNo"].ToString();
                    branch.Faxes = dr["Faxes"].ToString();
                    list.Add(branch);
                }
            }
            return list[0];
        }
        public void DeleteBranch(int id)
        {
            string sql = string.Format("delete Branch where BranchId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public void AddBranch(Branch branch)
        {
            string sql = "insert [Branch] (BranchName,BranchShortName,PrincipalUser,ConnectTelNo,ConnectMobileTelNo,Faxes)"
           + " values(@BranchName,@BranchShortName,@PrincipalUser,@ConnectTelNo,@ConnectMobileTelNo,@Faxes)";
            SqlParameter[] spa = {
                                     new SqlParameter("@BranchName",branch.BranchName),
                                     new SqlParameter("@BranchShortName",branch.BranchShortName),
                                     new SqlParameter("@PrincipalUser",branch.PrincipalUser),
                                     new SqlParameter("@ConnectTelNo",branch.ConnectTelNo),
                                     new SqlParameter("@ConnectMobileTelNo",branch.ConnectMobileTelNo),
                                     new SqlParameter("@Faxes",branch.Faxes)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void UpdateBranch(Branch branch)
        {
            string sql = "update [Branch] set BranchName=@BranchName,BranchShortName=@BranchShortName,PrincipalUser=@PrincipalUser,"
           + " ConnectTelNo=@ConnectTelNo,ConnectMobileTelNo=@ConnectMobileTelNo,Faxes=@Faxes where BranchId=@BranchId";
            SqlParameter[] spa = {
                                     new SqlParameter("@BranchId",branch.BranchId),
                                     new SqlParameter("@BranchName",branch.BranchName),
                                     new SqlParameter("@BranchShortName",branch.BranchShortName),
                                     new SqlParameter("@PrincipalUser",branch.PrincipalUser),
                                     new SqlParameter("@ConnectTelNo",branch.ConnectTelNo),
                                     new SqlParameter("@ConnectMobileTelNo",branch.ConnectMobileTelNo),
                                     new SqlParameter("@Faxes",branch.Faxes)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }

    }
}
